<?php defined('SYSPATH') or die('No direct access allowed.');

class Model_Supplier_Index extends ORM
{
    /**
     * @param $supplier_id
     * @return int[]
     * 订单数据
     */
    public static function account_info($supplier_id)
    {
        $account = array(
            'update_time' => 0,
            'pay_order' => 0,
            'pay_order_price' => 0,
            'account_price' => 0,
        );

        $pay_order = DB::select()->from('member_order')->where("status IN(2,5) and supplierlist={$supplier_id}")->order_by('addtime','ASC')->execute()->as_array();
        if($pay_order){
            $account['pay_order'] = count($pay_order);
            $account['update_time'] = $pay_order[$account['pay_order']-1]['addtime'];
            $order_id = array();
            $order_sn = array();
            foreach ($pay_order as $v){
                array_push($order_id,$v['id']);
                array_push($order_sn,$v['ordersn']);
            }
            $order_id = implode(',',$order_id);
            $order_sn = implode(',',$order_sn);
            $order_compute = DB::select()->from('member_order_compute')->where("order_id IN({$order_id})")->execute()->as_array();
            $brokerage     = DB::select()->from('supplier_brokerage')->where("ordersn IN({$order_sn})")->execute()->as_array();
            $account['pay_order_price'] = array_sum(array_column($order_compute,'total_price'));
            $account['account_price']   = array_sum(array_column($brokerage,'brokerage'));
        }

        return $account;
    }

    /**
     * @param $supplier_id
     * @return mixed|null
     * 供应商店铺信息
     */
    public static function supplier_shop($supplier_id)
    {
        $supplier = DB::select()->from('supplier')->where("id={$supplier_id}")->execute()->current();
        $authorization = DB::select()->from('model')->where("id IN({$supplier['authorization']})")->execute()->as_array();
        $supplier['authorization_name'] = implode('、',array_column($authorization,'modulename'));

        return $supplier;
    }

    /**
     * @param $supplier_id
     * @param $authorization
     * @return array|false
     * 供应商支持的分销渠道
     */
    public static function channel_info($supplier_id, $authorization)
    {
        $channel = Model_Supplier::get_ota_channel($supplier_id,true);
        foreach ($channel as &$v){
            $v['number'] = self::channel_product_num($v['id'], $authorization, $supplier_id);
        }
        return $channel;
    }

    /**
     * @param $channel_id
     * @param $authorization
     * @param $supplier_id
     * @return int[]
     * 查询各渠道的上架数量
     */
    public static function channel_product_num($channel_id, $authorization, $supplier_id)
    {
        $number = array(
            'shelves_num'     => 0,
            'the_shelves_num' => 0,
            'audit_num'       => 0,
        );
        $date = strtotime(date('Ymd'));
        $type_id = explode(',',$authorization);
        foreach ($type_id as $id){
            $id = $id>199?200:$id;
            $table = St_Functions::table_field($id);
            if(!empty($table[6])){
                $group_by = "GROUP BY a.id ";
                if($id!=5){
                    if($channel_id>1 && $id>199){
                        continue;
                    }
                    $channel_sql = $id>199?"":"AND b.supplier_channel_id={$channel_id}";
                    $sql = "SELECT a.* FROM sline_{$table[3]} AS a ";
                    $sql.= "LEFT JOIN sline_{$table[6]} AS b ON(a.id=b.{$table[1]}) ";
                    $sql.= "WHERE a.supplierlist={$supplier_id} {$channel_sql} AND b.day>={$date} ";
                    $shelves_sql     = $sql."AND a.ishidden=0 AND a.status=3 ".$group_by;
                    $the_shelves_sql = $sql."AND (a.ishidden=1 OR a.status IN(0,2,4)) ".$group_by;
                    $audit_sql       = $sql."AND a.status=1 ".$group_by;
                }else{
                    $sql = "SELECT a.* FROM sline_{$table[3]} AS a ";
                    $sql.= "LEFT JOIN sline_{$table[0]} AS b ON(a.id=b.{$table[1]}) ";
                    $sql.= "LEFT JOIN sline_{$table[6]} AS c ON(a.id=c.{$table[1]}) ";
                    $sql.= "WHERE b.supplierlist={$supplier_id} AND c.supplier_channel_id={$channel_id} AND c.day>={$date} ";
                    $shelves_sql     = $sql."AND a.ishidden=0 AND b.status=3 ".$group_by;
                    $the_shelves_sql = $sql."AND (a.ishidden=1 OR b.status IN(0,2,4)) ".$group_by;
                    $audit_sql       = $sql."AND b.status=1 ".$group_by;
                }
            }else{
                //为空的这些不支持供应其它渠道 所以仅在平台渠道时计算上下架数量
                if($channel_id==1){
                    $sql = "SELECT * FROM sline_{$table[3]} WHERE supplierlist={$supplier_id} ";
                    if($id==13){
                        $sql.= "AND endtime>={$date} ";
                    }
                    $shelves_sql     = $sql."AND ishidden=0 AND status=3 ";
                    $the_shelves_sql = $sql."AND (ishidden=1 OR status IN(0,2,4)) ";
                    $audit_sql       = $sql."AND status=1 ";
                }
            }
            if(!$shelves_sql && !$the_shelves_sql && !$audit_sql){
                continue;
            }
            $shelves_num     = DB::query(1, $shelves_sql)->execute()->count();
            $the_shelves_num = DB::query(1, $the_shelves_sql)->execute()->count();
            $audit_num       = DB::query(1, $audit_sql)->execute()->count();

            $number['shelves_num']     += $shelves_num;
            $number['the_shelves_num'] += $the_shelves_num;
            $number['audit_num']       += $audit_num;
        }

        return $number;
    }

    /**
     * @param $supplier_id
     * @return array
     * 组装筛选条件
     */
    public static function filter_criteria($supplier_id)
    {
        $data = array(
            'year' => array(date('Y')),
            'month' => range(1, 12),
            'type' => null,
        );
        /*获取支持筛选的年份和月份*/
        $min_year = DB::select(DB::expr('min(addtime) AS addtime'))->from('member_order')->where("supplierlist={$supplier_id}")->execute()->get('addtime');
        if($min_year){
            $min_year = date('Y',$min_year);
            if($min_year!=$data['year']){
                $year = array();
                for($i=$min_year;$i<=$data['year'][0];$i++){
                    array_push($year,$i);
                }
                rsort($year);
                $data['year'] = $year;
            }
        }
        /*获取支持的产品类型*/
        $authorization = DB::select()->from('supplier')->where("id={$supplier_id}")->execute()->get('authorization');
        if($authorization){
            $data['type'] = DB::select('id','modulename')->from('model')->where("id IN({$authorization})")->order_by('id','ASC')->execute()->as_array();
        }

        return $data;
    }

    public static function product_list($supplier_id, $type_id, $page, $keyword)
    {
        $table = St_Functions::table_field($type_id);
        $sql = $keyword?"AND title LIKE '%{$keyword}%' ":'';
        $query = DB::select('id','title')->from($table[3])->where("supplierlist={$supplier_id} {$sql}");
        $total = $query->execute()->count();
        $list = $query->offset($page)->limit(10)->execute()->as_array();

        return array(
            'data' => $list,
            'page_nums' => $total,
            'status' => $list?1:0
        );
    }

    /**
     * @param $supplier_id
     * @param $params
     * @return array
     * 按年查询订单量和收益
     */
    public static function data_statistics($supplier_id, $params)
    {
        $year = $params['year']?$params['year']:date('Y');
        if($params['month']>0){
            $firstDay = strtotime($year.'-'.$params['month']);
            $lastDay = strtotime(date('Y-m-d',$firstDay)." +1 month -1 day");
            $number = date("j",$lastDay);
            $x_data = json_encode(explode(',',implode('日,',range(1,$number)).'日'));
        }else{
            $number = 12;
            $x_data = json_encode(explode(',',implode('月,',range(1,$number)).'月'));
        }
        $data = array(
            'consume_num'   => array(),//核销数量
            'payment_num'   => array(),//支付数量
            'settle_profit' => array(),//已结算的收益
            'total_profit'  => array(),//总收益，含待结算
            'x_data'        => $x_data
        );
        for($i=0;$i<$number;$i++){
            if($params['month']>0){
                $firstDay += $i>0?86400:0;
                $lastDay = $firstDay+86400;
                if($firstDay>$lastDay){
                    break;
                }
            }else{
                $firstDay = strtotime($year.'-'.($i+1));
                $lastDay = strtotime(date('Y-m-d',$firstDay)." +1 month -0 day");
            }

            $consume_query = self::order_num_query($supplier_id, $firstDay, $lastDay, $params);
            $payment_query = self::order_num_query($supplier_id, $firstDay, $lastDay, $params);
            $data['consume_num'][$i] = $consume_query->where('status','=',5)->execute()->count();
            $data['payment_num'][$i] = $payment_query->where('status','in',array(2,5))->execute()->count();

            $settle_profit = self::settle_data_query($supplier_id, $firstDay, $lastDay, $params['product_id'], $params['type_id'], array('finish_brokerage','AND b.status=2 '));
            $total_profit = self::settle_data_query($supplier_id, $firstDay, $lastDay, $params['product_id'], $params['type_id'], array('brokerage','AND b.status IN(1,2) '));
            $data['settle_profit'][$i] = $settle_profit?$settle_profit:0;
            $data['total_profit'][$i] = $total_profit?$total_profit:0;
        }

        $data['consume_num']   = json_encode($data['consume_num']);
        $data['payment_num']   = json_encode($data['payment_num']);
        $data['settle_profit'] = json_encode($data['settle_profit']);
        $data['total_profit']  = json_encode($data['total_profit']);

        return $data;
    }

    /**
     * @param $supplier_id
     * @param $firstDay
     * @param $lastDay
     * @param $params
     * @return Database_Query_Builder_Select
     * 订单量查询
     */
    public static function order_num_query($supplier_id, $firstDay, $lastDay, $params)
    {
        $product_id = $params['product_id'];
        $type_id    = $params['type_id'];
        $channel_id = $params['channel_id'];
        $query = DB::select()->from('member_order')
            ->where("supplierlist",'=',$supplier_id)
            ->where('addtime','>=',$firstDay)
            ->where('addtime','<',$lastDay);
        if($product_id){
            $query->where('typeid','=',$type_id);
        }
        if($product_id && $type_id){
            $query->where('productautoid','=',$product_id);
        }
        if($channel_id>0){
            $query->where('channel_id','=',$channel_id);
        }
        return $query;
    }

    /**
     * @param $supplier_id
     * @param $firstDay
     * @param $lastDay
     * @param $product_id
     * @param $type_id
     * @param $sql_arr
     * @return mixed
     * 收益查询
     */
    public static function settle_data_query($supplier_id, $firstDay, $lastDay, $product_id, $type_id, $sql_arr)
    {
        $sql = "SELECT * FROM sline_member_order AS a ";
        $sql.= "LEFT JOIN sline_supplier_brokerage AS b ON(a.ordersn=b.ordersn) ";
        $sql.= "WHERE b.supplierlist={$supplier_id} AND b.addtime>={$firstDay} AND b.addtime<{$lastDay} ";
        if($type_id){
            $sql.= "AND a.typeid={$type_id} ";
        }
        if($product_id && $type_id){
            $sql.= "AND a.productautoid={$product_id} ";
        }
        $sql.= $sql_arr[1];
        $data = DB::query(1, str_replace('*',"sum(b.{$sql_arr[0]}) as price",$sql))->execute()->get('price');

        return $data;
    }

    public static function channel_statistics($supplier_id, $params)
    {
        $data = array(
            'channel'   => array(),
            'sales_num' => array(),
            'price'     => array()
        );
        $total_order_num = DB::select()->from('member_order')->where("supplierlist={$supplier_id} and status IN(2,5)")->execute()->count();


        $channel_list = Model_Supplier::get_ota_channel($supplier_id,true);
        foreach ($channel_list as $k=>$v){
            $data['channel'][$k]['name'] = $v['title'];
            $data['channel'][$k]['icon'] = 'circle';
            //$data['channel'][$k] = json_encode($data['channel'][$k]);
            $sales_num = 0;
            $price = 0;
            if($total_order_num>0){
                $sales_num = DB::select()->from('member_order')
                    ->where("supplierlist={$supplier_id} and status IN(2,5) and channel_id={$v['id']}")
                    ->execute()->count();

                $sql = "SELECT sum(b.supplier_commission) as price FROM sline_member_order AS a ";
                $sql.= "LEFT JOIN sline_member_order_compute AS b ON(a.id=b.order_id) ";
                $sql.= "WHERE a.supplierlist={$supplier_id} AND a.status IN(2,5) AND a.channel_id={$v['id']} ";
                $price = DB::query(1, $sql)->execute()->get('price');
            }
            if($sales_num>0){
                $data['sales_num'][$k]['value'] = $sales_num;
                $data['sales_num'][$k]['name'] = $v['title'];
                //$data['sales_num'][$k] = json_encode($data['sales_num'][$k]);
                $data['price'][$k]['value'] = $price?$price:0;
                $data['price'][$k]['name'] = $v['title'];
                //$data['price'][$k] = json_encode($data['price'][$k]);
            }
        }
        $data['channel']   = json_encode($data['channel']);
        $data['sales_num'] = json_encode($data['sales_num']);
        $data['price']     = json_encode($data['price']);

        return $data;
    }
}